1. Technical Field
This disclosure generally relates to computer database systems, and more specifically relates to a disappearing index for more efficient processing of a database query on a computer database.
2. Background Art
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. For example, an insurance company may have a database that includes all of its policy holders and their current account information, including payment history, premium amount, policy number, policy type, exclusions to coverage, etc. A database system allows the insurance company to retrieve the account information for a single policy holder among the thousands and perhaps millions of policy holders in its database.
Retrieval of information from a database is typically done using queries. A query usually specifies conditions that apply to one or more columns of the database, and may specify relatively complex logical operations on multiple columns. The database is searched for records that satisfy the query, and those records are returned as the query result, which is also known as a “result set”.
A database management system typically includes a query optimizer that attempts to optimize the performance of a query. The query optimizer selects from multiple query access plans to execute the query with the greatest efficiency. The query optimizer typically creates an access plan to execute the query. The access plan may use an index to access a database table. An index is an auxiliary data structure that is keyed to one or more columns of data in a database table to quickly access data in the table. However, even with an index a complex query to a large database can take a significant amount of resources to process the query.
In the prior art, the database query optimizer was forced to determine whether to use an existing index, or to create a temporary sparse index where the selection of the query is built into the temporary sparse index. There is a trade off between using an existing index versus a temporary sparse index. The existing index has no start up time to use it, but using the existing index may result in long fetch times because extra I/Os on a table object typically must be performed to determine which records to discard and which to return to the user. Conversely, the temporary sparse index has a start up cost to build, but once built, the fetch time will be faster. The fetch time is faster for a sparse index because every record processed in the temporary index is one of interest, and thus no time is wasted going to the table object for records that will be discarded immediately.